﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DreamBuilder.Common;
using DreamBuilder.Core.DAO;
using DreamBuilder.Core;
using System.Collections;
using System.Data.SqlClient;
using System.Data.Common;


namespace Web.SystemConfig.DataAccess
{
    public class DALAuth_User : DataAccessBase
    {
        IDBHelper helper = null;
        public DALAuth_User()
        {
            if (helper == null)
            {
                this.helper = DBHelperFactory.CreateInstance(DbHelperName.Business);
            }
        }

        internal int IsParentID()
        {
            string strsql = "SELECT COUNT(*) FROM P_Auth_User";
            int obj = (int)helper.ExecuteScalar(strsql);
            return obj;
        }

        internal DataTable SelectRolesByUserID(string userID)
        {
            string strSql = "";
            if (helper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {

                if (string.IsNullOrEmpty(userID))
                {
                    strSql = " select P_Auth_Role.Role_ID,P_Auth_Role.Role_Name,P_Auth_Role.Role_Type,0 as HasRole  from  P_Auth_Role";
                }
                else
                {
                    strSql = @" SELECT  
                          P_Auth_Role.Role_ID,  
                          P_Auth_Role.Role_Name,  
                          P_Auth_Role.Role_Type,
                          case when P_Auth_User_Role.User_ID is null then 0
                          else 1 end as HasRole  
                         FROM  
                          P_Auth_Role  
                         left join  
                          P_Auth_User_Role  
                         on  
                          P_Auth_Role.Role_ID = P_Auth_User_Role.Role_ID and P_Auth_User_Role.User_ID = '" + userID + "'  ";
                }
                return helper.ExecuteDataSet(strSql).Tables[0];
            }
            else
            {
                if (string.IsNullOrEmpty(userID))
                {
                    strSql = " select P_Auth_Role.Role_ID,P_Auth_Role.Role_Name,P_Auth_Role.Role_Type,cast(0 as bit) as HasRole  from  P_Auth_Role";
                }
                else
                {
                    strSql = @" SELECT  
                          P_Auth_Role.Role_ID,  
                          P_Auth_Role.Role_Name,  
                         P_Auth_Role.Role_Type,
                          case when P_Auth_User_Role.User_ID is null then cast(0 as bit)  
                          else cast(1 as bit) end as HasRole  
                         FROM  
                          P_Auth_Role  
                         left join  
                          P_Auth_User_Role  
                         on  
                          P_Auth_Role.Role_ID = P_Auth_User_Role.Role_ID and P_Auth_User_Role.User_ID = '" + userID + "'  ";
                }
                return helper.ExecuteDataSet(strSql).Tables[0];
            }

        }

        internal void InsertUserRole(string userID, string roleID)
        {
            string sSql = " INSERT INTO P_Auth_User_Role VALUES ('" + userID + "','" + roleID + "' )";
            helper.ExecuteSql(sSql);
        }

        internal void DeleteUserRole(string userID, string roleID)
        {
            string sSql = "DELETE FROM P_Auth_User_Role  where User_ID='" + userID + "' and Role_ID= '" + roleID + "'";
            helper.ExecuteSql(sSql);
        }

        internal DataTable SelectOrgRolesByUserID(string userID)
        {
            string strSql = "";
            if (helper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                if (string.IsNullOrEmpty(userID))
                {
                    strSql = " select P_Auth_Role.Role_ID,P_Auth_Role.Role_Name,0 as HasRole  from  P_Auth_Role";
                }
                else
                {
                    strSql = @"SELECT t1.Role_ID, t1.Role_Name, case when t2.user_id='" + userID + "' then 1 else 0 end  HasRole FROM P_Auth_Role t1 left join p_Auth_User_Role t2 on t1.role_id = t2.role_id";
                }
            }
            else
            {
                if (string.IsNullOrEmpty(userID))
                {
                    strSql = " select P_Auth_Role.Role_ID,P_Auth_Role.Role_Name,cast(0 as bit) as HasRole  from  P_Auth_Role";
                }
                else
                {
                    strSql = @" SELECT  
                          P_Auth_Role.Role_ID,  
                          P_Auth_Role.Role_Name,  
                          case when P_Auth_ORG_Role.ORG_ID is null then cast(0 as bit)  
                          else cast(1 as bit) end as HasRole  
                         FROM  
                          P_Auth_Role  
                         left join  
                          P_Auth_ORG_Role  
                         on  
                          P_Auth_Role.Role_ID = P_Auth_ORG_Role.Role_ID 
                          and P_Auth_ORG_Role.ORG_ID in ( select ORG_ID from P_Auth_ORG_User where P_Auth_ORG_User.User_ID = '" + userID + "' )";
                }
            }
            return helper.ExecuteDataSet(strSql).Tables[0];
        }
        internal DataTable GetUserInfo(string User_ID)
        {
            string strSql = "select * from P_Auth_User where User_ID = '" + User_ID + "'";
            return this.helper.ExecuteDataSet(strSql).Tables[0];
        }
        internal DataTable GetCheckOrg(string userID)
        {
            string strsql = "select P_Auth_ORG_User.ORG_ID,P_Auth_ORG.ORG_Name from dbo.P_Auth_ORG_User left join P_Auth_ORG on P_Auth_ORG_User.ORG_ID = P_Auth_ORG.ORG_ID where P_Auth_ORG_User.User_ID = '" + userID + "'";
            return this.helper.ExecuteDataSet(strsql).Tables[0];
        }
        internal void DeleteOrgWithoutOrgID(string userID)
        {
            string sqlstr = " delete from P_Auth_ORG_User where User_ID = '" + userID + "'";
            helper.ExecuteSql(sqlstr);
        }
        //by 刘英男
        internal DataTable GetUserOldOrg(string userID)
        {
            // string strSql = "select ORG_ID from P_Auth_User where User_ID = '" + userID + "'";
            string strSql = "select ORG_ID,Position_ID,Order_Num from P_Auth_User where User_ID = '" + userID + "'";
            return this.helper.ExecuteDataSet(strSql).Tables[0];
        }

        internal void AddOrg(string userID, string orgID)
        {
            //  string sqlstr = " insert into P_Auth_ORG_User(ORG_User_ID,ORG_ID,User_ID,Login_ID,User_Name,Can_Login,User_Status) select newid(),'" + orgID + "',User_ID,Login_ID,User_Name,Can_Login,Status_ID from P_Auth_User where User_ID = '" + userID + "' ";
            string sqlstr = " insert into P_Auth_ORG_User(ORG_User_ID,ORG_ID,User_ID,Login_ID,User_Name,Can_Login,User_Status,Position_ID,Order_Num) select newid(),'" + orgID + "',User_ID,Login_ID,User_Name,Can_Login,Status_ID,Position_ID,Order_Num from P_Auth_User where User_ID = '" + userID + "' ";
            helper.ExecuteSql(sqlstr);
        }

        internal int GetNewOrgIDCount(string userID, string orgID)
        {
            string strSql = "select count(*) from P_Auth_ORG_User where ORG_ID = '" + orgID + "' and User_ID = '" + userID + "'";
            return (int)helper.ExecuteScalar(strSql);
        }
        internal void DeleteOrg(string userID, string ORG_ID)
        {
            string sqlstr = " delete from P_Auth_ORG_User where ORG_ID = '" + ORG_ID + "' and User_ID = '" + userID + "'";
            helper.ExecuteSql(sqlstr);
        }

        internal void UpdateOrg(string userID, string orgID, string oldOrgID)
        {
            string sqlstr = " update P_Auth_ORG_User set ORG_ID = '" + orgID + "' where ORG_ID = '" + oldOrgID + "' and User_ID = '" + userID + "'";
            helper.ExecuteSql(sqlstr);
        }
        internal DataTable GetAllOrgData()
        {
            string strSql = "";
            if (helper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                //                strSql = @"select t.*, nvl(usercount,0) as usercount
                //                        from p_auth_org t
                //                        left outer join (Select U.ORG_ID,
                //                      count(U.ORG_USER_ID) as usercount
                //                      from P_Auth_ORG_User U
                //                      group by U.ORG_ID) t1 on t.org_id = t1.org_id ";
                strSql = @"select t.*, nvl(usercount,0) as usercount
                        from p_auth_org t
                        left outer join (Select U.ORG_ID,
                      count(U.Login_ID) as usercount
                      from P_Auth_User U
                      group by U.ORG_ID) t1 on t.org_id = t1.org_id where t.Status='Org_Status--1'";
            }
            else
            {
                strSql = "SELECT *,(select count(*) from dbo.P_Auth_User b where b.ORG_ID = P_Auth_ORG.ORG_ID) as usercount FROM P_Auth_ORG where [Status]!='Org_Status--0' ";

            }
            return helper.ExecuteDataSet(strSql).Tables[0];
        }
        internal DataTable GetORGInfo(string ORG_ID)
        {
            string strSql = " SELECT * FROM P_Auth_ORG where ORG_ID = '" + ORG_ID + "'";
            return helper.ExecuteDataSet(strSql).Tables[0];
        }
        internal void DeleteUserRole(string pkValue)
        {
            string sqlstr = " delete P_Auth_User_Role where User_ID = '" + pkValue + "'";
            helper.ExecuteSql(sqlstr);
        }

        internal void DeleteUserOrg(string pkValue)
        {
            string sqlstr = " delete P_Auth_ORG_User where User_ID = '" + pkValue + "'";
            helper.ExecuteSql(sqlstr);
        }

        internal void DeleteUser(string pkValue)
        {
            string sqlstr = " delete P_Auth_User where User_ID = '" + pkValue + "'";
            DBHelper.ExecuteSql(sqlstr);
        }
        //by 刘英男
        internal void UpdatePosition(string userID, string PositionID, string oldPositionID)
        {
            string sqlstr = " update P_Auth_ORG_User set Position_ID = '" + PositionID + "' where Position_ID = '" + oldPositionID + "' and User_ID = '" + userID + "'";
            helper.ExecuteSql(sqlstr);
        }
        internal void UpdateOrderNum(string userID, string OrderNumID, string oldOrderNumID)
        {
            string sqlstr = " update P_Auth_ORG_User set Order_Num = '" + OrderNumID + "' where Order_Num = '" + oldOrderNumID + "' and User_ID = '" + userID + "'";
            helper.ExecuteSql(sqlstr);
        }
        //上移下移用户 by刘英男
        internal void MoveUpUser(string pkValue)
        {
            string sqlstr = " update P_Auth_User set Order_Num = Order_Num-1 where User_ID = '" + pkValue + "'";
            helper.ExecuteSql(sqlstr);
        }
        internal void MoveDownUser(string pkValue)
        {
            string sqlstr = " update P_Auth_User set Order_Num = Order_Num+1 where User_ID = '" + pkValue + "'";
            helper.ExecuteSql(sqlstr);
        }
        //add by 刘英男 根据所属单位获取最大的排序字段
        internal Int32 GetCountUserNum(string orgID)
        {
            string strSql = "select count(*) from P_Auth_User where ORG_ID = '" + orgID + "'";
            return Convert.ToInt32(helper.ExecuteScalar(strSql).ToString());
        }
        internal Int32 GetMaxOrgNum(string orgID)
        {
            //string strSql = "select Max(Order_Num) from P_Auth_User where ORG_ID = '" + orgID + "' group by ORG_ID";
            string strSql = "select Max(Order_Num) from P_Auth_User";
            return Convert.ToInt32(helper.ExecuteScalar(strSql).ToString());
        }
        //add by 刘英男 交换排序字段
        internal void ChangUserOrder(string userorder, string useridgrid)
        {
            string sql1 = string.Empty;
            sql1 = sql1 + " Declare @curOrdNo int";
            sql1 = sql1 + " Select @curOrdNo=Order_Num From P_Auth_User Where User_ID='" + useridgrid + "' ";
            sql1 = sql1 + " Update P_Auth_User Set Order_Num=(Select Order_Num From P_Auth_User Where User_ID='" + userorder + "') Where User_ID='" + useridgrid + "' ";
            sql1 = sql1 + " Update P_Auth_User Set Order_Num=@curOrdNo Where User_ID='" + userorder + "' ";
            helper.ExecuteSql(sql1);
        }
        #region add by 刘英男 检查用户登录名是否唯一
        /// <summary>
        ///  检查用户登录名是否唯一
        /// </summary>
        /// <param name="loginid">登录名</param>
        /// <returns>数量</returns>
        internal int CheckLoginID(string loginid)
        {
            string sql = "select Count(*) from p_auth_user where login_id='" + loginid + "'";
            int obj = (int)int.Parse(helper.ExecuteScalar(sql).ToString());
            return obj;
        }
        #endregion
        #region add by 刘英男 检查用户主键是否唯一
        /// <summary>
        ///  检查用户主键是否唯一
        /// </summary>
        /// <param name="userCheckID">用户登录id</param>
        /// <returns>数量</returns>
        internal int CheckUserPrimary(string userCheckID)
        {
            string sql = "select Count(*) from p_auth_user where user_id='" + userCheckID + "'";
            int obj = (int)int.Parse(helper.ExecuteScalar(sql).ToString());
            return obj;
        }
        #endregion
        #region add by liuyingnan 获取最大的排序字段
        /// <summary>
        ///   获取最大的排序字段
        /// </summary>
        /// <param name="userCheckID">用户登录id</param>
        /// <returns>数值</returns>
        internal Int32 GetMaxPositionNum()
        {
            string strSql = "select Max(Order_Num) from p_bt_position";
            object obj = helper.ExecuteScalar(strSql);
            if (obj != null)
                return Convert.ToInt32(obj);
            else
            {
                return 1;
            }
        }
        #endregion
        #region add by 刘英男 检查职务排序序号是否唯一
        /// <summary>
        ///  检查职务排序序号是否唯一
        /// </summary>
        /// <param name="userOrder">登录名</param>
        /// <returns>是，否</returns>
        internal int CheckPositionOrder(string userOrder)
        {
            string sql = "select Count(*) from p_bt_position where Order_Num='" + userOrder + "'";
            int obj = (int)int.Parse(helper.ExecuteScalar(sql).ToString());
            return obj;
        }
        #endregion
        #region add by lyn 20111027
        internal DataTable CheckPositionNameInfo(string positionName)
        {
            string strSql = "select PositionName from P_BT_Position where PositionName = '" + positionName + "'";
            return helper.ExecuteDataSet(strSql).Tables[0];
        }
        #endregion
        #region add by 刘英男 20111028检查OA用户登录名是否唯一
        /// <summary>
        ///  检查用户登录名是否唯一
        /// </summary>
        /// <param name="OAloginid">登录名</param>
        /// <returns>数量</returns>
        internal int CheckOALoginID(string OAloginid)
        {
            string sql = "select Count(*) from p_auth_user where OAlogin_id='" + OAloginid + "'";
            int obj = (int)int.Parse(helper.ExecuteScalar(sql).ToString());
            return obj;
        }
        #endregion
        #region add by lyn 获取最大的排序字段 20111117
        /// <summary>
        ///   获取最小的排序字段根据部门
        /// </summary>
        /// <param name="userCheckID">用户登录id</param>
        /// <returns>数值</returns>
        internal Int32 GetMinOrgNum(string orgID)
        {
            string strSql = "select Min(Order_Num) from P_Auth_User where ORG_ID = '" + orgID + "' group by ORG_ID";
            return Convert.ToInt32(helper.ExecuteScalar(strSql).ToString());
        }
        #endregion
        #region add by lyn 批量转移用户 20111128
        /// <summary>
        ///   批量转移用户
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <param name="orgId">单位id</param>
        internal void UpdateUserTransferDeptInfo(string userId, string orgId)
        {
            string strSql = "update p_auth_user set org_id = '" + orgId + "' where user_id = '" + userId + "'";
            DBHelper.ExecuteSql(strSql);
        }
        #endregion
        #region add by lyn 更新用户个人信息 20111201
        /// <summary>
        ///   更新用户个人信息
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <param name="userName">用户名</param>
        /// <param name="mobile">电话</param>
        /// <param name="fax">传真</param>
        /// <param name="email">电子邮件</param>
        /// <param name="postCode">邮编</param>
        /// <param name="idNumber">身份证号</param>
        /// <param name="vpnNumber">VPN用户名</param>
        /// <param name="erpNumber">ERP号</param>
        /// <param name="insuranceNumber">保险编号</param>
        internal void UpdateUserPersonalInfo(string userId, string userName, string mobile, string officePhone, string fax, string email, string postCode, string idNumber, string vpnNumber, string erpNumber, string insuranceNumber, string address)
        {
            string strSql = "update p_auth_user set user_name='" + userName + "' ,mobile='" + mobile + "',office_phone='" + officePhone + "',fax='" + fax + "',email='" + email + "',postcode='" + postCode + "',idnumber='" + idNumber + "',vpnnumber='" + vpnNumber + "',erpnumber='" + erpNumber + "',InsuranceNumber='" + insuranceNumber + "',address='" + address + "' where user_id='" + userId + "' ";
            DBHelper.ExecuteSql(strSql);
        }
        #endregion

        #region add by guoyin  查询组织机构下的所有用户
        /// <summary>
        /// 查询组织机构下的所有用户
        /// </summary>
        /// <param name="OrgID">组织机构ID</param>
        /// <returns></returns>
        internal DataTable GetOrgUsers(string OrgID)
        {
            string sql = "select * from P_Auth_User where Org_ID='" + OrgID + "'";
            return helper.ExecuteDataSet(sql).Tables[0];
        }

        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <returns></returns>
        internal DataTable GetAllUsers()
        {
            string sql = "select * from P_Auth_User ";
            return helper.ExecuteDataSet(sql).Tables[0];
        }
        #endregion

        #region 根据用户id查找所有单位信息 add by lyn 20111223
        /// <summary>
        /// 根据用户id查找所有单位信息
        /// </summary>
        /// <param name="OrgID">组织机构ID</param>
        /// <returns></returns>
        internal DataTable CheckOrgInfo(string userID)
        {
            string sql = "select a.ORG_ID,a.ORG_Name from p_auth_org a,p_auth_user b where a.org_id=b.org_id and b.user_id='" + userID + "'";
            return helper.ExecuteDataSet(sql).Tables[0];
        }
        #endregion
        #region 修改登录人，登录时间，修改人，修改时间 add by lyn 20120108
        /// <summary>
        /// 修改登录人，登录时间，修改人，修改时间
        /// </summary>
        /// <param name="tableName">数据库表名</param>
        /// <param name="User">数数库中用户名</param>
        /// <param name="userName">用户名</param>
        /// <param name="Time">数据库中时间</param>
        /// <param name="currentTime">当前时间</param>
        /// <param name="primarykey">数据库主键</param>
        /// <param name="pkValue">主键</param>
        /// <returns></returns>
        internal void ModifyUserInfo(string tableName, string User, string userName, string Time, DateTime currentTime, string primarykey, string pkValue)
        {
            //string strSql = string.Empty;
            //if (this.DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            //{
            //     strSql = "update " + tableName + " set " + User + "='" + userName + "'," + Time + "=to_date('" + currentTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd HH24:mi:ss') where " + primarykey + "='" + pkValue + "'";
            //}
            //else
            //{
            //     strSql = "update " + tableName + " set " + User + "='" + userName + "'," + Time + "='" + currentTime + "' where " + primarykey + "='" + pkValue + "'";
            //}
            //    DBHelper.ExecuteSql(strSql);
        }
        #endregion        // by lyn
        //update oa_system_signoptionlist set orderno=(select max(orderno)+1 from oa_system_signoptionlist) where signopid='914866c9-6fbd-4da1-9787-908350ca10c3'
        #region 新增数据,更新排序字段 add by lyn 20120112
        /// <summary>
        /// 新增数据,更新排序字段
        /// </summary>
        /// <param name="tableName">数据库表名</param>
        /// <param name="OrderNum">数据库排序字段</param>
        /// <param name="primarykey">数据库主键</param>
        /// <param name="pkValue">记录主键</param>
        /// <returns></returns>
        internal void UpdateOrderNumInfo(string tableName, string orderNum, string primarykey, string pkValue)
        {
            string strSql = "update " + tableName + " set " + orderNum + " =(select max(" + orderNum + ")+1 from " + tableName + ") where " + primarykey + " = '" + pkValue + "'";
            DBHelper.ExecuteSql(strSql);
        }
        #endregion
        #region 根据著作权id获取附件 add by lyn 20110202
        /// <summary>
        /// 根据著作权id获取附件
        /// </summary>
        /// <param name="SFID"></param>
        /// <returns></returns>
        internal DataTable GetAccBySFID(string tableName, string SFID)
        {
            string strSql = "select * from dbo.P_BT_Attachments where ObjectTableName='" + tableName + "' and ObjectID='" + SFID + "' ";
            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }
        #endregion
        #region 获取下载附件的信息 add by lyn 20111213
        /// <summary>
        /// 获取下载附件的信息
        /// </summary>
        /// <param name="fileID">附件ID</param>
        /// <param name="TID">收发文ID</param>
        /// <param name="TName">发文OR收文</param>
        /// <returns>附件信息</returns>
        internal DataTable GetUploadFileInfo(string fileID)
        {
            DataTable dt = new DataTable();
            string strSql = string.Empty;
            strSql = "select attachmentname as [FileName],attachmentcontent as [Document],documenttype as DocType from P_BT_Attachments where AttachmentID=@fileID";
            DbParameter[] dbParam;
            dbParam = new DbParameter[]{
                new SqlParameter("@fileID",fileID.Trim())                
            };
            dt = DBHelper.ExecuteDataSet(strSql, dbParam).Tables[0];

            return dt;
        }
        #endregion
        #region add by lyn 20120419 给用户增加普通用户的角色
        public void UpdateUserRoleInfo()
        {
            StringBuilder strSql = new StringBuilder("insert into p_auth_user_role ");
            strSql.Append("select user_id,'PTYG' AS role_id from p_auth_user ");
            strSql.Append("where user_id not in(select user_id from p_auth_user_role where role_id='PTYG') ");
            strSql.Append("and status_id='User_Status--1' ");
            strSql.Append("and login_id iS not null and login_id!='NULL' and Email is not null ");
            DBHelper.ExecuteSql(strSql.ToString());
        }
        #endregion

        /// <summary>
        /// 获取某单位某角色的用户信息
        /// </summary>
        /// <param name="orgid"></param>
        /// <param name="roleID"></param>
        /// <returns></returns>
        internal DataTable GetUserDtByOrgIDAndRoleID(string orgid, string roleID)
        {
            DataTable rtnDataTable = null;
            string sql = string.Format(@"select distinct * from p_auth_user u
                                                        join p_auth_user_role ur
                                                        on u.org_id='{0}'
                                                        and u.User_ID=ur.User_ID
                                                        and ur.Role_id='{1}'", orgid, roleID);
            DataSet oDataSet = DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnDataTable = oDataSet.Tables[0];
            }
            return rtnDataTable;
        }

        internal DataTable GetUserDtByQZHAndToRoleID(Guid userID, string roleID)
        {
            DataTable rtnDataTable = null;
            string sql = string.Format(@"select *
  from p_Auth_User_Role ur, p_auth_user u
 where u.User_ID = ur.User_ID
   and u.Org_ID in
       (select Org_ID
          from p_auth_org o
         where o.Org_DESC =
               (select p.Org_DESC
                  from p_auth_org p
                 where p.Org_ID =
                       (select Org_ID
                          from p_auth_user
                         where User_ID =
                               '{0}')))
                               and ur.Role_ID={1}", userID, roleID);
            DataSet oDataSet = DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnDataTable = oDataSet.Tables[0];
            }
            return rtnDataTable;
        }

        internal DataTable GetUserDtByQZHAndToRoleID(string qzh, string roleID)
        {
            DataTable rtnDataTable = null;
            string sql = string.Format(@"select u.user_id from p_auth_user u
inner join p_auth_org o
on u.org_id=o.org_id
inner join p_auth_user_role ur
on u.user_id=ur.user_id
where o.org_desc='{0}'
and ur.role_id='{1}'", qzh, roleID);
            DataSet oDataSet = DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnDataTable = oDataSet.Tables[0];
            }
            return rtnDataTable;
        }

        internal string GetQZHByUserID(string userID)
        {
            string sql = string.Format(@"select org_desc
  from p_auth_org o
 inner join p_auth_user u
    on o.org_id = u.org_id
 where u.user_id = '{0}'", userID);
            var result = DBHelper.ExecuteScalar(sql);
            return result == null ? string.Empty : result.ToString();
        }
    }
}
